1. Field of the Invention
The present invention relates to a method, system, and program for implementing scrollable cursors in a distributed database system.
2. Description of the Related Art
Prior art database programs include a feature referred to as cursors. A cursor is a named control structure used by an application program to point to a row of interest within some set of rows and to retrieve rows from the set, possibly making updates and deletions. A cursor points to rows from a database table that satisfy a structured query language (SQL) query against the table. The rows in the table that satisfy the SQL query comprise a result table of data. The SQL query includes an SQL SELECT statement and a WHERE clause to qualify rows according to a predicate. An application can then access data on a row-by-row basis from the result table.
When a cursor is opened or initialized, the current row position (current cursor position) is before the first row in the result table. The application program may issue fetch commands to move the current row position (current cursor position) and retrieve row data. Cursors are described in SQL099 standard and also the Open Database Connectivity (ODBC) architecture. Cursors may be updateable or non-updateable (read-only). An updateable cursor allows the application program to update or delete the row at the current cursor position—this is known as updating or deleting through the cursor. A non-updateable (read-only) cursor does not allow the application program to perform such operations.
Cursors may be serial (non-scrollable) or scrollable. A serial (or non-scrollable) cursor is one that only allows the application to move forward through the result table. A scrollable cursor is one that allows the application program to move both forward and backward through the result table. Fetching forward in the result table increases the current cursor position while fetching backward decreases the current cursor position.
Cursors may be insensitive or sensitive to updates. An insensitive cursor is one that does not show updates made to the underlying data for the cursor, whether the update is made through the cursor or by other concurrent processes. Otherwise, the cursor is sensitive to updates. There may be degrees of sensitivity defined by the implementation, but this patent does not distinguish degrees of sensitivity. Fetches against the cursor may be single-row fetches or multi-row fetches. A single-row fetch returns only one row in response to the fetch request. A multi-row fetch returns a specified number of rows in response to the fetch. It is possible for a database system to support single-row fetching without supporting multi-row fetching.
In a distributed relational database environment, an application program at a client computer may request a cursor from a database at a server computer. In the current art, the Distributed Relational Database Architecture (DRDA) specifies a protocol for a client computer to open a cursor and request data from a cursor result table from a database at a server. Because the overhead of communicating across the network is high, DRDA clients and servers attempt to minimize the number of messages they exchange. In the current art, when a DRDA server receives a single-row fetch request for a read-only non-scrollable cursor, it fetches ahead additional rows and returns all the fetched rows in a single buffer, known as a query block. The query block is of a size negotiated between the client and the server. Because the application can only move forward sequentially through the cursor, when the client receives the query block, it satisfies the application requests for rows from the cursor by reading the locally available query block, thus saving additional message flows across the network. The protocol is known in DRDA as Limited Block Query Protocol. For updateable non-scrollable cursors, fetching ahead is not possible since a row may be updated after it has been prefetched by the server but before it has been fetched by the application. In this case, the DRDA Fixed Row Query Protocol is used. If the client and/or the server support only single-row fetch, this typically means that each remote fetch request retrieves one row of data at a time.
When an application presents a window to a user displaying rows of data and allows the user to scroll forward and backwards, scrollable cursors are the most appropriate structure by which to implement the application. Typically, these modern windowing applications are interactive, requiring quick responses to actions in the window. Techniques used in prior art database systems introduce performance or concurrency problems for such scrollable cursor applications. For instance, the client application may frequently change the cursor position within the window. In such case, the client cannot practically communicate with the server each time the client's window position is changed. Updateable cursors often employ techniques like DRDA Fixed Row Protocol to send only one row per network transmission. This guarantees the server data is unchanged between the fetch operation and any subsequent update. However, the network transmission overhead for this approach is prohibitive for query answer sets of any reasonable size. “Wide” cursors that fetch multiple rows on a single operation alleviate the network performance problems, but introduce concurrency problems because all of the rows within the cursor are locked for updateable queries.
Thus, there is a need in the art to provide an improved method for fetching cursor data in a client/server environment.